﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace ExcelColumnSplitter
{
    public partial class SplitNavigatorForm : Form
    {
        private Worksheet Sheet { get; set; }
        private int SourceColumnIndex { get; set; }
        private int TargetColumnIndex { get; set; }
        public SplitNavigatorForm()
        {
            InitializeComponent();
        }

        private Presets presets;

        public SplitNavigatorForm(Presets presets)
        {
            this.presets = presets;
            InitializeComponent();
        }

        private List<string> Separators { get; set; } = new List<string>();
        private void AddSeparatorButton_Click(object sender, EventArgs e)
        {
            if (SelectedIndex == -1)
            {
                var separator = GetAutoSeparator();
                Separators.Add(separator);
                SeparatorListBox.Items.Add(string.IsNullOrEmpty(separator) ? "请点击此处修改" : separator);
                SeparatorListBox.SelectedIndex = Separators.Count - 1;
            }
            else
            {
                var separator = GetAutoSeparator();
                Separators.Insert(SelectedIndex + 1, separator);
                SeparatorListBox.Items.Insert(SelectedIndex + 1, string.IsNullOrEmpty(separator) ? "请点击此处修改" : separator);
                SeparatorListBox.SelectedIndex = SelectedIndex + 1;
            }
            UpdatePreview();
        }
        private string GetAutoSeparator()
        {
            var checkList = new List<string> { ",", "，", ";", "|" };
            // remove that in Separators
            foreach (var separator in Separators)
            {
                checkList.Remove(separator);
            }
            int maxRows = Sheet.UsedRange.Row + Sheet.UsedRange.Rows.Count - 1;
            for (int i = 2; i <= maxRows && i < 100; i++)
            {
                var value = Sheet.Cells[i, SourceColumnIndex]?.Value;
                if (value != null && !string.IsNullOrEmpty(value.ToString()))
                {
                    foreach (var check in checkList)
                    {
                        if (value.ToString().Contains(check))
                        {
                            return check;
                        }
                    }
                }
            }
            return "";
        }

        private int SelectedIndex { get; set; } = -1;
        private void SelectSeparatorItem(int index)
        {
            SelectedIndex = index;
            if (index != -1)
            {
                // enable textbox and button
                SeparatorTextBox.Enabled = true;
                RemoveSeparatorButton.Enabled = true;
                // set text
                SeparatorTextBox.Text = Separators[index];
            }
        }
        private void SeparatorListBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            SelectSeparatorItem(SeparatorListBox.SelectedIndex);
        }

        private void SeparatorTextBox_TextChanged(object sender, EventArgs e)
        {
            if (SelectedIndex != -1)
            {
                // set separator in list
                Separators[SelectedIndex] = SeparatorTextBox.Text;
                // set separator in listbox
                SeparatorListBox.Items[SelectedIndex] = SeparatorTextBox.Text;
                UpdatePreview();
            }
        }

        private void RemoveSeparatorButton_Click(object sender, EventArgs e)
        {
            if (SelectedIndex != -1)
            {
                Separators.RemoveAt(SelectedIndex);
                SeparatorListBox.Items.RemoveAt(SelectedIndex);
                SelectedIndex = -1;
                // disable textbox and button
                SeparatorTextBox.Enabled = false;
                RemoveSeparatorButton.Enabled = false;
                UpdatePreview();
            }
        }

        private void SeparatorListBox_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            SeparatorTextBox.Focus();
        }

        private List<Extraction> Extractions { get; set; } = new List<Extraction>();
        private int ExtractionSelectedIndex { get; set; } = -1;
        private void AddExtractionButton_Click(object sender, EventArgs e)
        {
            if (ExtractionSelectedIndex == -1)
            {
                Extractions.Add(new Extraction { Name = "未命名" });
                ExtractionListBox.Items.Add("未命名");
                ExtractionListBox.SelectedIndex = Extractions.Count - 1;
            }
            else
            {
                Extractions.Insert(ExtractionSelectedIndex + 1, new Extraction { Name = "未命名" });
                ExtractionListBox.Items.Insert(ExtractionSelectedIndex + 1, "未命名");
                ExtractionListBox.SelectedIndex = ExtractionSelectedIndex + 1;
            }
            UpdatePreview();
        }

        private void RemoveExtractionButton_Click(object sender, EventArgs e)
        {
            if (ExtractionSelectedIndex != -1)
            {
                Extractions.RemoveAt(ExtractionSelectedIndex);
                ExtractionListBox.Items.RemoveAt(ExtractionSelectedIndex);
                ExtractionSelectedIndex = -1;
                // disable textbox and button
                NewColumnNameTextBox.Enabled = false;
                RemoveExtractionButton.Enabled = false;
                LeftStringTextBox.Enabled = false;
                RightStringTextBox.Enabled = false;
                PostProcessingComboBox.Enabled = false;
                AvoidDuplicateTextBox.Enabled = false;
                UpdatePreview();
            }
        }

        private void SelectExtractionItem(int index)
        {
            ExtractionSelectedIndex = index;
            if (index != -1)
            {
                // enable textbox and button
                NewColumnNameTextBox.Enabled = true;
                RemoveExtractionButton.Enabled = true;
                LeftStringTextBox.Enabled = true;
                RightStringTextBox.Enabled = true;
                PostProcessingComboBox.Enabled = true;
                AvoidDuplicateTextBox.Enabled = true;
                // set text
                NewColumnNameTextBox.Text = Extractions[index].Name;
                LeftStringTextBox.Text = Extractions[index].LeftSeparator;
                RightStringTextBox.Text = Extractions[index].RightSeparator;
                PostProcessingComboBox.SelectedIndex = (int)Extractions[index].PostProcess;
                AvoidDuplicateTextBox.Text = Extractions[index].AvoidDuplicate;
            }
        }

        private void ExtractionListBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            SelectExtractionItem(ExtractionListBox.SelectedIndex);
        }

        private void NewColumnNameTextBox_TextChanged(object sender, EventArgs e)
        {
            if (ExtractionSelectedIndex != -1)
            {
                Extractions[ExtractionSelectedIndex].Name = NewColumnNameTextBox.Text;
                ExtractionListBox.Items[ExtractionSelectedIndex] = NewColumnNameTextBox.Text;
                UpdatePreview();
            }
        }

        private void LeftStringTextBox_TextChanged(object sender, EventArgs e)
        {
            if (ExtractionSelectedIndex != -1)
            {
                Extractions[ExtractionSelectedIndex].LeftSeparator = LeftStringTextBox.Text;
                UpdatePreview();
            }
        }

        private void RightStringTextBox_TextChanged(object sender, EventArgs e)
        {
            if (ExtractionSelectedIndex != -1)
            {
                Extractions[ExtractionSelectedIndex].RightSeparator = RightStringTextBox.Text;
                UpdatePreview();
            }
        }

        private void PostProcessingComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ExtractionSelectedIndex != -1)
            {
                Extractions[ExtractionSelectedIndex].PostProcess = (PostProcess)PostProcessingComboBox.SelectedIndex;
                UpdatePreview();
            }
        }


        private void AvoidDuplicateTextBox_TextChanged(object sender, EventArgs e)
        {
            if (ExtractionSelectedIndex != -1)
            {
                Extractions[ExtractionSelectedIndex].AvoidDuplicate = AvoidDuplicateTextBox.Text;
                UpdatePreview();
            }
        }

        private void ExtractionListBox_DoubleClick(object sender, EventArgs e)
        {
            NewColumnNameTextBox.Focus();
        }

        private string[] provinces = { "河北", "山西", "黑龙江", "吉林", "辽宁", "江苏", "浙江", "安徽", "福建", "江西", "山东", "河南", "湖北", "湖南", "广东", "海南", "四川", "贵州", "云南", "陕西", "甘肃", "青海", "台湾", "内蒙古", "广西", "西藏", "宁夏", "新疆", "北京", "天津", "上海", "重庆", "香港", "澳门" };

        public static int ColumnLetterToColumnIndex(string columnLetter)
        {
            // I don't know how to do this in Excel VSTO project...
            // keep only alpha letters
            columnLetter = Regex.Replace(columnLetter, "[^A-Z]", "");
            columnLetter = columnLetter.ToUpper();
            int sum = 0;

            for (int i = 0; i < columnLetter.Length; i++)
            {
                sum *= 26;
                sum += (columnLetter[i] - 'A' + 1);
            }
            return sum;
        }

        private IEnumerable<List<List<string>>> Divide(int startRow, int endRow)
        {
            // get right most column index
            int maxColumns = Sheet.UsedRange.Column + Sheet.UsedRange.Columns.Count - 1;
            bool needCheckDuplicate = Extractions.Any(x => !string.IsNullOrEmpty(x.AvoidDuplicate));
            // for each cell in source column
            for (int i = startRow; i <= endRow; i++)
            {
                // get cell value
                object cellValue = Sheet.Cells[i, SourceColumnIndex]?.Value;
                string cellValueString = cellValue?.ToString();
                var row = new List<List<string>>();
                // split cell value string
                string[] splitted = new[] { cellValueString };
                if (Separators.Count > 0)
                {
                    splitted = cellValueString?.Split(
                        Separators.Where(x => !string.IsNullOrEmpty(x)).ToArray(),
                        StringSplitOptions.None
                    ) ?? (FilterEmptyInputCheckBox.Checked ? new string[0] : new string[] { "" });
                }
                var currentColumnIndex = TargetColumnIndex;
                // for each splitted string
                foreach (string s in splitted)
                {
                    var group = new List<string>();
                    // extract from s
                    foreach (Extraction extraction in Extractions)
                    {
                        // get left index
                        int leftIndex = 0;
                        if (!string.IsNullOrEmpty(extraction.LeftSeparator))
                        {
                            leftIndex = s.IndexOf(extraction.LeftSeparator);
                            if (leftIndex == -1)
                            {
                                continue;
                            }
                            leftIndex += 1;
                        }
                        int rightIndex = s.Length;
                        if (!string.IsNullOrEmpty(extraction.RightSeparator))
                        {
                            rightIndex = s.IndexOf(extraction.RightSeparator, leftIndex);
                            if (rightIndex == -1)
                            {
                                continue;
                            }
                        }

                        // get extracted string
                        string extracted = s.Substring(leftIndex, rightIndex - leftIndex);
                        // post process
                        switch (extraction.PostProcess)
                        {
                            case PostProcess.None:
                                break;
                            case PostProcess.Province:
                                var flag = false;
                                foreach (string province in provinces)
                                {
                                    if (extracted.Contains(province))
                                    {
                                        extracted = province;
                                        flag = true;
                                        break;
                                    }
                                }
                                if (!flag)
                                {
                                    extracted = "";
                                }
                                break;
                            default:
                                break;
                        }
                        // set result
                        group.Add(extracted);
                        currentColumnIndex++;
                    }
                    // check avoid duplicate
                    if (needCheckDuplicate)
                    {
                        bool skip = false;
                        for (int j = 0; j < group.Count; j++)
                        {
                            if (!string.IsNullOrEmpty(Extractions[j].AvoidDuplicate))
                            {
                                int columnIndex = ColumnLetterToColumnIndex(Extractions[j].AvoidDuplicate);
                                var targetCellValue = Sheet.Cells[i, columnIndex]?.Value?.ToString();
                                if (group[j] == targetCellValue)
                                {
                                    skip = true;
                                    break;
                                }
                            }
                        }
                        if (skip)
                        {
                            continue;
                        }
                    }
                    // check empty
                    if (FilterEmptyResultCheckBox.Checked && group.All(x => string.IsNullOrEmpty(x)))
                    {
                        // do not add to row
                        continue;
                    }
                    row.Add(group);
                }
                // check limitation of groups number
                if (GroupLimitCheckBox.Checked)
                {
                    // get limit number
                    if (int.TryParse(GroupLimitTextBox.Text, out int limit))
                    {
                        if (row.Count > limit)
                        {
                            row.RemoveRange(limit, row.Count - limit);
                        }
                    }
                }
                yield return row;
            }
        }

        private static bool ShouldMarkedAsText(string text)
        {
            // 如果都是数字，并且存在前导零，则应该标记为文本
            return Regex.IsMatch(text, @"^\d+$") && (text.StartsWith("0") && !text.StartsWith("0."));
        }

        private void DivideToColumnsButton_Click(object sender, EventArgs e)
        {
            StatusLabel.Text = "处理中...";
            // get largest row index
            int maxRows = Sheet.UsedRange.Row + Sheet.UsedRange.Rows.Count - 1;
            int maxGroupNumber = 0;
            var result = Divide(2, maxRows);
            int currentColumnIndex;
            int rowIndex = 2;
            List<bool> markedText = Enumerable.Repeat(false, Sheet.UsedRange.Columns.Count).ToList();
            foreach (var row in result)
            {
                currentColumnIndex = TargetColumnIndex;
                foreach (var group in row)
                {
                    var extractedIndex = 0;
                    foreach (var extracted in group)
                    {
                        if (ShouldMarkedAsText(extracted))
                        {
                            Sheet.Cells[rowIndex, currentColumnIndex].NumberFormat = "@";
                            markedText[extractedIndex] = true;
                        }
                        Sheet.Cells[rowIndex, currentColumnIndex] = extracted;
                        currentColumnIndex++;
                        extractedIndex++;
                    }
                    if (group.Count > maxGroupNumber)
                    {
                        maxGroupNumber = group.Count;
                    }
                }
                if (row.Count > maxGroupNumber)
                {
                    maxGroupNumber = row.Count;
                }
                rowIndex++;
            }
            // set column name
            for (int i = 0; i < maxGroupNumber; i++)
            {
                for (int j = 0; j < Extractions.Count; j++)
                {
                    var value = Extractions[j].Name;
                    if (maxGroupNumber > 1)
                    {
                        value += (i + 1).ToString();
                    }
                    Sheet.Cells[1, TargetColumnIndex + i * Extractions.Count + j] = value;
                    if (markedText[j])
                    {
                        Sheet.Columns[TargetColumnIndex + i * Extractions.Count + j].NumberFormat = "@";
                    }
                }
            }
            // show message
            StatusLabel.Text = "分列完成";
        }

        private void DivideToRowsButton_Click(object sender, EventArgs e)
        {
            StatusLabel.Text = "处理中...";
            // copy current sheet to new sheet
            Worksheet newSheet = Globals.ThisAddIn.Application.Worksheets.Add();
            // get largest row index
            int maxRows = Sheet.UsedRange.Row + Sheet.UsedRange.Rows.Count - 1;
            // get right most column index
            int maxColumns = Sheet.UsedRange.Column + Sheet.UsedRange.Columns.Count - 1;
            List<bool> markedText = Enumerable.Repeat(false, Sheet.UsedRange.Columns.Count).ToList();
            // set column name
            for (int i = 1; i <= maxColumns; i++)
            {
                newSheet.Cells[1, i] = Sheet.Cells[1, i].Value;
            }
            for (int i = 0; i < Extractions.Count; i++)
            {
                newSheet.Cells[1, TargetColumnIndex + i] = Extractions[i].Name;
            }
            var sourceRowIndex = 2;
            var rowIndex = 2;
            int currentColumnIndex;
            foreach (var row in Divide(2, maxRows))
            {
                foreach (var group in row)
                {
                    currentColumnIndex = TargetColumnIndex;
                    // copy to new sheet
                    for (int j = 1; j <= maxColumns; j++)
                    {
                        newSheet.Cells[rowIndex, j].NumberFormat = Sheet.Cells[sourceRowIndex, j].NumberFormat;
                        newSheet.Cells[rowIndex, j] = Sheet.Cells[sourceRowIndex, j]?.Value;
                    }
                    var extractedIndex = 0;
                    foreach (var extracted in group)
                    {
                        if (ShouldMarkedAsText(extracted))
                        {
                            newSheet.Cells[rowIndex, currentColumnIndex].NumberFormat = "@";
                            markedText[extractedIndex] = true;
                        }
                        newSheet.Cells[rowIndex, currentColumnIndex] = extracted;
                        currentColumnIndex++;
                        extractedIndex++;
                    }
                    rowIndex++;
                }
                sourceRowIndex++;
            }
            // format
            for (int j = 0; j < Extractions.Count; j++)
            {
                if (markedText[j])
                {
                    newSheet.Columns[TargetColumnIndex + j].NumberFormat = "@";
                }
            }
            // show message
            StatusLabel.Text = "分行完成";
        }

        private void SplitNavigatorForm_Deactivate(object sender, EventArgs e)
        {
            try
            {
                if (!openInputBox)
                {
                    Opacity = 0.5;
                }
            }
            catch (Exception)
            {
                // I don't know why this happens
            }
        }

        private void SplitNavigatorForm_Activated(object sender, EventArgs e)
        {
            Opacity = 1.0;
        }

        private bool SelectArea(string title, string description, out Range range)
        {
            Microsoft.Office.Interop.Excel.Application excelApp = Globals.ThisAddIn.Application;
            // get current selected range
            Range currentRange = excelApp.Selection;
            object result = Globals.ThisAddIn.Application.InputBox(description, title, currentRange.Address, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 8);
            range = null;
            if (result is Range)
            {
                range = result as Range;
                range.Select();
                return true;
            }
            return false;
        }

        private void RefreshEnabled()
        {
            var enabled = SourceColumnIndex > 0 && TargetColumnIndex > 0;
            DivideToColumnsButton.Enabled = enabled;
            DivideToRowsButton.Enabled = enabled;
        }

        private void SelectSourceArea()
        {
            if (!SelectArea("选择数据源", "在Excel中点击选择需要拆分的列中的一个单元格", out Range range))
            {
                return;
            }
            Activate();
            SourceColumnIndex = range.Column;
            RefreshEnabled();
            // set text to formula
            SourceTextBox.Text = range.EntireColumn.Address.Split(':').FirstOrDefault();
        }

        private void SourceTextBox_Click(object sender, EventArgs e)
        {
            SelectSourceArea();
            UpdatePreview();
        }

        private void SelectTargetArea()
        {
            if (!SelectArea("选择存放位置", "在Excel中点击选择存放数据的开始列中的一个单元格", out Range range))
            {
                return;
            }
            Activate();
            TargetColumnIndex = range.Column;
            RefreshEnabled();
            TargetTextBox.Text = range.EntireColumn.Address.Split(':').FirstOrDefault();
        }

        private void TargetTextBox_Click(object sender, EventArgs e)
        {
            SelectTargetArea();
        }

        private void UpdatePreview()
        {
            // get example source cell
            int maxRows = Sheet.UsedRange.Row + Sheet.UsedRange.Rows.Count - 1;
            int exampleRowIndex = 0;
            for (int i = 2; i <= maxRows && i < 1000; i++)
            {
                var value = Sheet.Cells[i, SourceColumnIndex]?.Value;
                if (value != null && !string.IsNullOrEmpty(value.ToString()))
                {
                    exampleRowIndex = i;
                    break;
                }
            }
            // add columns to PreviewDataGridView
            PreviewDataGridView.Columns.Clear();
            foreach (var extraction in Extractions)
            {
                PreviewDataGridView.Columns.Add(extraction.Name, extraction.Name);
            }
            // apply to example row
            PreviewDataGridView.Rows.Clear();
            if (exampleRowIndex > 0)
            {
                var result = Divide(exampleRowIndex, exampleRowIndex).FirstOrDefault();
                if (result != null)
                {
                    foreach (var group in result)
                    {
                        var row = new DataGridViewRow();
                        foreach (var extracted in group)
                        {
                            row.Cells.Add(new DataGridViewTextBoxCell() { Value = extracted });
                        }
                        PreviewDataGridView.Rows.Add(row);
                    }
                }
            }
        }

        private void SplitNavigatorForm_Load(object sender, EventArgs e)
        {
            Sheet = Globals.ThisAddIn.Application.ActiveSheet;
            if (presets == null)
            {
                TopMost = false;
                SelectSourceArea();
                SelectTargetArea();
                TopMost = true;
                if (Sheet == null)
                {
                    return;
                }
            }
            else
            {
                SourceTextBox.Text = presets.SourceAddress;
                SourceColumnIndex = ColumnLetterToColumnIndex(presets.SourceAddress);
                TargetTextBox.Text = presets.TargetAddress;
                TargetColumnIndex = ColumnLetterToColumnIndex(presets.TargetAddress);
                RefreshEnabled();
                Separators = presets.Separators;
                foreach (var separator in Separators)
                {
                    SeparatorListBox.Items.Add(separator);
                }
                Extractions = presets.Extractions;
                foreach (var extraction in Extractions)
                {
                    ExtractionListBox.Items.Add(extraction.Name);
                }
                FilterEmptyInputCheckBox.Checked = presets.FilterEmptyInput;
                FilterEmptyResultCheckBox.Checked = presets.FilterEmptyResult;
                GroupLimitCheckBox.Checked = presets.EnableGroupLimit;
                if (presets.EnableGroupLimit)
                {
                    GroupLimitTextBox.Text = presets.GroupLimit.ToString();
                }
                UpdatePreview();
            }
            if (SourceColumnIndex != 0)
            {
                object tableHeader = Sheet.Cells[1, SourceColumnIndex]?.Value;
                string header = "";
                if (tableHeader != null && !string.IsNullOrEmpty(tableHeader.ToString()))
                {
                    header = tableHeader.ToString();
                }
                if (string.IsNullOrEmpty(header))
                {
                    Text = "分列向导";
                    header = "未命名";
                }
                else
                {
                    Text = "分列向导 - " + header;
                }
                if (Extractions.Count == 0)
                {
                    Extractions.Add(new Extraction { Name = header });
                    ExtractionListBox.Items.Add(header);
                }
            }
            UpdatePreview();
        }

        private void GroupLimitCheckBox_CheckedChanged(object sender, EventArgs e)
        {
            if (GroupLimitCheckBox.Checked)
            {
                GroupLimitTextBox.Enabled = true;
            }
            else
            {
                GroupLimitTextBox.Enabled = false;
            }
            UpdatePreview();
        }

        private void GroupLimitTextBox_TextChanged(object sender, EventArgs e)
        {
            UpdatePreview();
        }

        private void AvoidDuplicateTextBox_Click(object sender, EventArgs e)
        {
            if (!SelectArea("选择需要避免重复的列", "在Excel中点击选择目标列中的一个单元格", out Range range))
            {
                return;
            }
            Activate();
            AvoidDuplicateTextBox.Text = range.EntireColumn.Address.Split(':').FirstOrDefault();
        }

        private bool openInputBox = false;
        private void SaveToPresetsButton_Click(object sender, EventArgs e)
        {
            // winform dialog input name
            openInputBox = true;
            TopMost = false;
            string name = Microsoft.VisualBasic.Interaction.InputBox("请输入名称", "保存为预设", "新预设", -1, -1);
            TopMost = true;
            openInputBox = false;
            if (string.IsNullOrEmpty(name))
            {
                return;
            }
            // save to presets
            int.TryParse(GroupLimitTextBox.Text, out var groupLimit);
            var presets = new Presets
            {
                EnableGroupLimit = GroupLimitCheckBox.Checked,
                Extractions = Extractions,
                FilterEmptyInput = FilterEmptyInputCheckBox.Checked,
                FilterEmptyResult = FilterEmptyResultCheckBox.Checked,
                GroupLimit = groupLimit,
                Separators = Separators,
                SourceAddress = SourceTextBox.Text,
                TargetAddress = TargetTextBox.Text
            };
            AppSettings.Instance.Presets[name] = presets;
            AppSettings.Instance.Save();
        }
    }
}
